{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 独立使用django model（一）\n",
    "# Django Shell\n",
    "# 项目根目录中打开Django Shell : python manage.py shell\n",
    "# import django\n",
    "# django.setup()  # 装载Django\n",
    "\n",
    "# 独立使用django model（二）\n",
    "# python ×××.py or Jupyter Notebook\n",
    "# 将脚本放置在项目根目录下jupyter_notebook文件中比较方便\n",
    "import sys\n",
    "import os\n",
    "import json\n",
    "import requests\n",
    "from django.core.files import File\n",
    "from django.core.files.base import ContentFile\n",
    "# 外部脚本链接django项目\n",
    "# 添加环境变量\n",
    "'''\n",
    "print(os.path.abspath('__file__'))\n",
    "print(os.path.dirname(os.path.abspath('__file__')))\n",
    "print(os.path.dirname(os.path.dirname(os.path.abspath('__file__'))))\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook/__file__\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis/jupyter_notebook\n",
    "/Users/zhaojinhui/Desktop/webapp/backend/rmis\n",
    "'''\n",
    "project = os.path.dirname(os.getcwd())  # get current work directory\n",
    "sys.path.append(project)\n",
    "sys.path.append(os.path.join(project, 'rmis'))\n",
    "# sys.path.append已设置临时环境变量，可以直接调用其中的文件，脚本是外部脚本，只是放在了项目当中而已\n",
    "os.environ.setdefault('DJANGO_SETTINGS_MODULE', 'settings')\n",
    "# 相关数据库配置等都在settings.py文件中\n",
    "# 导入并装载django\n",
    "import django\n",
    "django.setup()\n",
    "# 脚本正文"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>price_sell</th>\n",
       "      <th>price_purchase</th>\n",
       "      <th>id</th>\n",
       "      <th>amount</th>\n",
       "      <th>quantity</th>\n",
       "      <th>interests</th>\n",
       "      <th>fk_good</th>\n",
       "      <th>rate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>99</td>\n",
       "      <td>49</td>\n",
       "      <td>284880</td>\n",
       "      <td>2178</td>\n",
       "      <td>22</td>\n",
       "      <td>1100</td>\n",
       "      <td>864</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>129</td>\n",
       "      <td>64</td>\n",
       "      <td>43820</td>\n",
       "      <td>516</td>\n",
       "      <td>4</td>\n",
       "      <td>260</td>\n",
       "      <td>44</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>149</td>\n",
       "      <td>74</td>\n",
       "      <td>21913</td>\n",
       "      <td>298</td>\n",
       "      <td>2</td>\n",
       "      <td>150</td>\n",
       "      <td>6</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>199</td>\n",
       "      <td>99</td>\n",
       "      <td>65739</td>\n",
       "      <td>796</td>\n",
       "      <td>4</td>\n",
       "      <td>400</td>\n",
       "      <td>94</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>200</td>\n",
       "      <td>100</td>\n",
       "      <td>65737</td>\n",
       "      <td>1000</td>\n",
       "      <td>5</td>\n",
       "      <td>500</td>\n",
       "      <td>261</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>249</td>\n",
       "      <td>124</td>\n",
       "      <td>153308</td>\n",
       "      <td>2490</td>\n",
       "      <td>10</td>\n",
       "      <td>1250</td>\n",
       "      <td>274</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>499</td>\n",
       "      <td>249</td>\n",
       "      <td>21892</td>\n",
       "      <td>499</td>\n",
       "      <td>1</td>\n",
       "      <td>250</td>\n",
       "      <td>4</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   price_sell  price_purchase      id  amount  quantity  interests  fk_good  \\\n",
       "0          99              49  284880    2178        22       1100      864   \n",
       "1         129              64   43820     516         4        260       44   \n",
       "2         149              74   21913     298         2        150        6   \n",
       "3         199              99   65739     796         4        400       94   \n",
       "4         200             100   65737    1000         5        500      261   \n",
       "5         249             124  153308    2490        10       1250      274   \n",
       "6         499             249   21892     499         1        250        4   \n",
       "\n",
       "   rate  \n",
       "0  0.51  \n",
       "1  0.50  \n",
       "2  0.50  \n",
       "3  0.50  \n",
       "4  0.50  \n",
       "5  0.50  \n",
       "6  0.50  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from invoice.models import GoodInSale\n",
    "from good.models import Good\n",
    "from django.forms.models import model_to_dict\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "goodsinsale  = [ model_to_dict(good) for good in GoodInSale.objects.filter(fk_sale__fk_store=5)[:30] ]\n",
    "goods = [ model_to_dict(good) for  good in Good.objects.all() ]\n",
    "\n",
    "df_goodinsale = pd.DataFrame.from_records(goodsinsale)\n",
    "df_good = pd.DataFrame.from_records(goods)\n",
    "\n",
    "df_goodinsale = df_goodinsale.loc[:, [\"id\",\"created\",\"amount\",\"quantity\",\"interests\",\"fk_good\"]]\n",
    "\n",
    "df_good = df_good.loc[:, [\"id\",\"price_sell\",\"price_purchase\"]]\n",
    "df_good = df_good.set_index(\"id\")  # inplace\n",
    "\n",
    "df_price = df_goodinsale.join(df_good, on=\"fk_good\").dropna()\n",
    "\n",
    "df_price = df_price.groupby([\"price_sell\",\"price_purchase\"]).sum()\n",
    "# df_price_quantity.groupby([\"price_sell\",\"price_purchase\"]).describe()\n",
    "# df_price.agg([np.sum, np.mean, np.std])\n",
    "\n",
    "df_price = df_price.reset_index()\n",
    "df_price['rate'] = round(df_price['interests'] /  df_price['amount'] ,2)\n",
    "df_price\n",
    "\n",
    "# data_price_amount = df_price.loc[:,[\"price_purchase\", \"price_sell\",\"amount\"]].to_dict(orient=\"split\")[\"data\"]\n",
    "# data_price_interests = df_price.loc[:,[\"price_purchase\", \"price_sell\",\"interests\"]].to_dict(orient=\"split\")[\"data\"]\n",
    "# data_price_quantity = df_price.loc[:,[\"price_purchase\", \"price_sell\",\"quantity\"]].to_dict(orient=\"split\")[\"data\"]\n",
    "# data_price_rate = df_price.loc[:,[\"price_purchase\", \"price_sell\",\"rate\"]].to_dict(orient=\"split\")[\"data\"]\n",
    "# print(data_price_amount,data_price_interests,data_price_quantity, data_price_rate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
